Mikmak MySQL DML Category

CRUD stored procedures voor de Category tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam. Voor het maken van de stored procedures baseren we ons op het Mikmak logisch model.

Design

Oplossing

Insert

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML Insert Stored Procedure for Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategoryInsert;
DELIMITER //
CREATE PROCEDURE `CategoryInsert`
(
IN pName NVARCHAR (255) ,
IN pDescription NVARCHAR (1024) ,
OUT pId INT
)
BEGIN
INSERT INTO `Category`
(
  `Category`.`Name`,
  `Category`.`Description`
)
VALUES
(
  pName,
  pDescription
);
SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

De stored procedure testen

use Webshop;
call CategoryInsert ('Nederlandse boeken', 'Vertalingen, Nederlandse en Vlaamse auteurs', @Id);
select @Id;
call CategoryInsert ('Banken & Fauteuils', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Tafels & Stoelen', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Kasten', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Bureaus', 'Werken in stijl', @Id);
select @Id;
call CategoryInsert ('Bedden & Matrassen', 'Slapen in stijl', @Id);
select @Id;
call CategoryInsert ('Woonaccessoires', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Beddengoed', 'Slapen in stijl', @Id);
select @Id;
call CategoryInsert ('Badtextiel', 'Wassen in stijl', @Id);
select @Id;
call CategoryInsert ('Raamdecoratie', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Verlichting', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Vloerkleden', 'Wonen in stijl', @Id);
select @Id;
call CategoryInsert ('Kinderkamer', 'Wonen in stijl', @Id);
select @Id;

Update

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML Update Stored Procedure for Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategoryUpdate;
DELIMITER //
CREATE PROCEDURE `CategoryUpdate`
(
pName NVARCHAR (255) ,
pDescription NVARCHAR (1024) ,
pId INT
)
BEGIN
UPDATE `Category`
SET
  `Name` = pName,
  `Description` = pDescription
WHERE `Category`.`Id` = pId;
END //
DELIMITER ;

De stored procedure testen

use Webshop;
call CategoryUpdate (
    'Verlichting',    
    'Comfortabel werken en wonen',
    (select Id from Category where Name = ' Verlichting') );

Delete

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML Delete Stored Procedure for Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategoryDelete;
DELIMITER //
CREATE PROCEDURE `CategoryDelete`
(
  pId INT
)
BEGIN
DELETE FROM `Category`
WHERE `Category`.`Id` = pId;
END //
DELIMITER ;

De stored procedure testen

SelectOne

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML SelectOne Stored Procedure for Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategorySelectOne;
DELIMITER //
CREATE PROCEDURE `CategorySelectOne`
(
  pId INT
)
BEGIN
SELECT * FROM `Category`
WHERE `Category`.`Id` = pId;
END //
DELIMITER ;

De stored procedure testen

SelectAll

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML SelectAll Stored Procedure for table Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategorySelectAll;
DELIMITER //
CREATE PROCEDURE `CategorySelectAll`
(
)
BEGIN
SELECT `Category`.`Name`,
`Category`.`Id`
FROM `Category`
ORDER BY `Name`;
END //
DELIMITER ;

De stored procedure testen

SelectByName

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 03:02:06 PM
-- DML SelectByName Stored Procedure for table Category
--
USE Webshop;
DROP PROCEDURE IF EXISTS CategorySelectByName;
DELIMITER //
CREATE PROCEDURE `CategorySelectByName`
(
  pName NVARCHAR (255)
)
BEGIN
SELECT `Category`.`Name`,
`Category`.`Id`

 FROM `Category`
WHERE `Category`.`Name` = pName
ORDER BY `Category`.`Name`;
END //
DELIMITER ;

De stored procedure testen